﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using NPOI.SS.UserModel;
using System.IO;
using System.Data;
using Model;
using NPOI.HPSF;
using NPOI.HSSF.UserModel;
using System.Web.UI;
using System.Text;

namespace PMS.Web
{
    public class ExportFiles : Page
    {
        #region Excel导出
        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="sheetName">Excel文件名</param>
        /// <param name="dtshow">数据集</param>
        public void ExportExcel(string sheetName, DataTable dtshow)
        {
            string filename = HttpUtility.UrlEncode(sheetName + DateTime.Now.ToString("yyyy-MM-dd") + ".xls", Encoding.GetEncoding("utf-8")).ToString();
            Response.ContentType = "application/vnd.ms-excel";
            Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", filename));
            Response.Clear();

            InitializeWorkbook();
            GenerateData(dtshow, sheetName);
            Response.BinaryWrite(WriteToStream().GetBuffer());
            Response.End();
        }

        HSSFWorkbook hssfworkbook;
        void InitializeWorkbook()
        {
            hssfworkbook = new HSSFWorkbook();

            ////create a entry of DocumentSummaryInformation
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
            dsi.Company = "NPOI Team";
            hssfworkbook.DocumentSummaryInformation = dsi;

            ////create a entry of SummaryInformation
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
            si.Subject = "NPOI SDK Example";
            hssfworkbook.SummaryInformation = si;
        }

        MemoryStream WriteToStream()
        {
            //Write the stream data of workbook to the root directory
            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);
            return file;
        }

        void GenerateData(DataTable dtshow, string sheetName)
        {
            //获取模版
            List<Templete> excelList = BLL.TempleteBLL.GetTemplete(sheetName);
            Sheet sheet1 = hssfworkbook.CreateSheet(sheetName);

            #region 列设置

            //设置字体
            Font font0 = hssfworkbook.CreateFont();
            font0.Boldweight = 700;

            //居中设置
            CellStyle cellstyle0 = hssfworkbook.CreateCellStyle();
            cellstyle0.Alignment = HorizontalAlignment.CENTER;
            cellstyle0.BorderBottom = CellBorderType.THIN;
            cellstyle0.BorderLeft = CellBorderType.THIN;
            cellstyle0.BorderRight = CellBorderType.THIN;
            cellstyle0.BorderTop = CellBorderType.THIN;
            cellstyle0.SetFont(font0);

            //居右设置
            CellStyle cellstyle1 = hssfworkbook.CreateCellStyle();
            cellstyle1.Alignment = HorizontalAlignment.RIGHT;
            cellstyle1.BorderBottom = CellBorderType.THIN;
            cellstyle1.BorderLeft = CellBorderType.THIN;
            cellstyle1.BorderRight = CellBorderType.THIN;
            cellstyle1.BorderTop = CellBorderType.THIN;

            //居左设置
            CellStyle cellstyle2 = hssfworkbook.CreateCellStyle();
            cellstyle2.Alignment = HorizontalAlignment.LEFT;
            cellstyle2.BorderBottom = CellBorderType.THIN;
            cellstyle2.BorderLeft = CellBorderType.THIN;
            cellstyle2.BorderRight = CellBorderType.THIN;
            cellstyle2.BorderTop = CellBorderType.THIN;

            #endregion

            Row row0 = sheet1.CreateRow(0);
            //表头设置
            for (int i = 0; i < excelList.Count; i++)
            {
                row0.CreateCell(i).SetCellValue(excelList[i].columnName);
                //sheet1.SetColumnWidth(i, excelList[i].columnWidth);
                row0.GetCell(i).CellStyle = cellstyle0;
            }

            //数据绑定
            for (int i = 0; i < dtshow.Rows.Count; i++)
            {
                Row row = sheet1.CreateRow(i + 1);
                for (int j = 0; j < excelList.Count; j++)
                {
                    #region 数据绑定
                    string columnName = excelList[j].columnName + "";
                    string dataName = excelList[j].dataName + "";
                    string formatStr = excelList[j].formatStr + "";
                    object result = dtshow.Rows[i][dataName];
                    if (!string.IsNullOrEmpty(formatStr))
                    {
                        result = string.Format(formatStr, result);
                    }

                    if (columnName == "序号")
                    {
                        result = (i + 1).ToString();
                    }
                    row.CreateCell(j).SetCellValue(result.ToString());
                    #endregion
                }
            }
        }

        #endregion
    }
}